package org.sgx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.sgx.dao.Dao.ModelAccessor;
import org.sgx.dao.model.Model;
import org.sgx.dao.util.BeanUtil;
import org.sgx.dao.util.ConcreteStringMatcher;
import org.sgx.dao.util.OrderedMap;
import org.sgx.dao.util.SQLUtil;
import org.sgx.dao.util.StringMatcher;
import org.sgx.dao.util.Util;



/**
 * 
 * @author sg
 *
 */
public abstract class AbstractDao<T extends Model> implements Dao<T> {
	
	
	
protected ConnectionGiver connectionGiver;
protected SQLUtil sql = null;

public AbstractDao(ConnectionGiver connectionGiver) throws DaoException {
	this.connectionGiver=connectionGiver;
	sql = new SQLUtil(connectionGiver);
}
	

	
	
/* *** sql info and utilities *** */
	
protected OrderedMap<String, String> sqlColumns;
	
public void createTable() throws DaoException {
	if(!sql.tableExists(getTableName())) {
		sql.tableCreate(getTableName(), sqlColumns);
	}
}	
public ConnectionGiver getConnectionGiver() {
		return connectionGiver;
	}
	public void setConnectionGiver(ConnectionGiver connectionGiver) {
		this.connectionGiver = connectionGiver;
	}
protected Connection getConnection() throws DaoException {
	return getConnectionGiver().getConnection();
}
protected long incrementCounter() throws DaoException {
	return sql.incrementCounter(getModelClassName());//this.getClass().getName());
}







/* *** imodel instances and model class info *** */

protected ModelAccessor<T> modelAccessor;

public ModelAccessor<T> getModelAccessor() {
	return modelAccessor;
}
public OrderedMap<String, String> getSQLColumns() {
	return sqlColumns;
}
Class<T> modelClass=null;
public Class<T> getModelClass() throws ClassNotFoundException {
	if(modelClass==null)
		modelClass = (Class<T>) AbstractDao.class.getClassLoader().loadClass(getModelClassName());
	return modelClass;
}
public T instantiateModel() throws DaoException {
	try {
	return getModelClass().newInstance();
	} catch (Exception e) {
		throw new DaoException(e);
	}
}
protected void setModelId(Model m, Object val) throws DaoException {
	try {
		BeanUtil.setProperty(m, m.getFieldIdName(), val);
	} catch (Exception e) {
		throw new DaoException(e);
	}
}
protected Long getModelId(Model m) throws DaoException {
	try {
		return (Long) BeanUtil.getProperty(m, m.getFieldIdName());
	} catch (Exception e) {
		throw new DaoException(e);
	}
}





/* *** internationalization utilities *** */

public static final String I18N_COLUMN_NAME = "__i18n";
private void checkI18n() throws DaoException {
	if(!sql.tableHasColumn(getTableName(), I18N_COLUMN_NAME)) {
		sql.tableAddColumn(getTableName(), I18N_COLUMN_NAME, SQLUtil.TYPE_STRING);
		//set default locale to all rows
		sql.update(getTableName(), null, Util.<Object>toOrderedMap(I18N_COLUMN_NAME, SQLUtil.I18N_DEFAULT_LOCALE));
	}
}

/**
 * inserts or updates a locale for an object. If the id of T is null or <=0, the object will be added as a new object in the db. 
 * Then, search for object with id, locale.- if found update, else add. 
 * @param t
 * @param locale
 * @return the id of t. in the case of a new object created (t.id==null||t.id<=0), the new generated id is returned. 
 * @throws DaoException
 */
public Long setLocale(T t, String locale) throws DaoException {
	checkI18n();
	Long id = getModelId(t);
	if(id==null||id<=0||/* user wants to add the new object t */
			get(id)==null) { 
		add(t);
		id=getModelId(t);
	}
	List<Map> r = sql.query(getTableName(),  Util.<Object>toOrderedMap(
		getColumnIdName(), id, 
		I18N_COLUMN_NAME, locale));
	
	List<String> colNames = ((OrderedMap<String, String>)getSQLColumns().clone()).getOrderedKeys();//.clone();
	OrderedMap<Object, Object> m = Util.<Object>toOrderedMap();
	for(String cname : colNames) {
		m.put(cname, getModelAccessor().get(t, cname));
	}
	m.put(I18N_COLUMN_NAME, locale);
	if(r.size()==0) { /* there is no item {id, locale} */
//		long newId = incrementCounter();
//		m.put(t.getFieldIdName(), newId);
		sql.insert(m, getTableName());
//		return false;
	}
	else {
		sql.update(getTableName(), Util.<Object>toOrderedMap(
			getColumnIdName(), getModelId(t), 
			I18N_COLUMN_NAME, locale), m);
//		return true;
	}
	return id;
}
public List<T> getAllLocale(String locale) throws DaoException {
	if(locale==null)
		return listAll();
	try {
		checkI18n();
		List<T> l = new LinkedList<T>();
		Connection con = getConnection();
		PreparedStatement st = con.prepareStatement(
			"select * FROM "+getTableName()+
			" WHERE "+I18N_COLUMN_NAME+"=?");
		st.setString(1, locale);
		ResultSet rs = st.executeQuery();
		T e = buildFrom(rs);
		while(e!=null) {
			l.add(e);
			e = buildFrom(rs);
		}		
		sql.sqlClose(rs, st, con);
		return l;
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}
/**
 * if object t is not defined in some locale of allLocales array, then it is added so t will be the localized object for those locales. does nothing if t is already localized.
 * @throws DaoException 
 */
public void checkAllLocales(T t, String[] allLocales) throws DaoException {
	Long id = getModelId(t);
	if(get(id)==null)
		return;
	for (int i = 0; i < allLocales.length; i++) {
		if(getLocale(id, allLocales[i])==null) {
			setLocale(t, allLocales[i]);
		}
	}
}
public T getLocale(Long id, String locale) throws DaoException {
	checkI18n();
	
	try {
		if(locale==null)
			locale=SQLUtil.I18N_DEFAULT_LOCALE; 
		Connection con = getConnection();		
		String q = "SELECT * FROM "+getTableName()+" WHERE "+getColumnIdName()+"=? " +
				"AND "+I18N_COLUMN_NAME+"=?";
		PreparedStatement st = con.prepareStatement(q);
		st.setLong(1, id);
		st.setString(2, locale);
		ResultSet rs = st.executeQuery();
		T e = buildFrom(rs);
//		System.out.println("getLocale. id: "+id+", locale: "+locale+", return: "+e);
		sql.sqlClose(rs, st, con);
		return e;
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}
public List<T> getLocales(List<Long>ids, String locale) throws DaoException {
	List<T>l = new LinkedList<T>();
	for(Long id : ids) {
		l.add(getLocale(id, locale));
	}
	return l;
}
//public boolean updateLocale(T t, String locale) throws DaoException {
//	
//};


/* *** operation implementations *** */

public Long add(T t) throws DaoException {
//	if(locale!=null) 
//		checkI18n();
	long id = incrementCounter();
	setModelId(t, id);
	save(t, true);
	return id;
}
//public long add(T t) throws DaoException {
//	return add(t, null);
//}
public Long[] add(T... t) throws DaoException {
	Long[] ids = new Long[t.length];
	for (int i = 0; i < ids.length; i++) {
		ids[i]=add(t[i]);
	}
	return ids;
};
public T get(Long id) throws DaoException {
	return load(id);
}
//public T get(long id, String locale) throws DaoException {
//	return load(id, locale);
//}
/**
 * TODO: can this be done more efficient?
 */
public List<T> get(List<Long> ids) throws DaoException {
	List<T> l = new LinkedList<T>();
	for(Long id : ids) {
		T t = get(id);
		if(t!=null)
			l.add(t);
	}
	return l;
}

public T load(Long id) throws DaoException {
	try {
		if(id==null||id<=0)
			return null;
//		if(locale!=null)
//			checkI18n();
		
		Connection con = getConnection();
		
		String q = "SELECT * FROM "+getTableName()+" WHERE "+getColumnIdName()+"=?";
//		if(locale!=null)
//			q += " AND "+I18N_COLUMN_NAME+"=?";
		
//		System.out.println("LOAD: "+q);
		PreparedStatement st = con.prepareStatement(q);
		st.setLong(1, id);
//		if(locale!=null)
//			st.setString(2, locale);
		
		ResultSet rs = st.executeQuery();//.executeQuery(q);
		T e = buildFrom(rs);
		sql.sqlClose(rs, st, con);
		return e;
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}
//public T load(long id) throws DaoException {
//	return load(id, null);
//}


/**
 * inserts an obvject to the data base. if insert==true, the object is inserted, if false, the object is updated.
 **/
protected long save(T model, boolean insert) throws DaoException {
	try {
//		if(locale!=null)
//			checkI18n();
		Connection con = getConnection();
		PreparedStatement st = null;			
		String q = null;
		if(insert) {
			q = "INSERT INTO "+getTableName()+"(";
			String vals=" VALUES (";
			Iterator<String> it = getSQLColumns().getOrderedKeys().iterator();
			while(it.hasNext()) {
				String name = it.next();
				q += name;
				vals += "?";
				if(it.hasNext()) {
					q+=", ";
					vals+=", ";
				}
			}
//			if(locale!=null) {
//				q+=", "+I18N_COLUMN_NAME;
//				vals+=" ? ";
//			}
			q+=")";
			vals+=")";
			q=q+" "+vals;
			
		} else {
			q = "UPDATE "+getTableName()+" SET ";
			Iterator<String> it = getSQLColumns().getOrderedKeys().iterator();
			while(it.hasNext()) {
				String name = it.next();
				q += name+"=?";
				if(it.hasNext()) {
					q+=", ";
				}
			}
//			if(locale!=null)
//				q+=", "+I18N_COLUMN_NAME+"='"+locale+"'";
			
			q+=" WHERE "+getColumnIdName()+"="+getModelId(model);
			
//			if(locale!=null) 
//				q+=" AND "+I18N_COLUMN_NAME+"='"+locale+"'";				
			
		}		
		//load and execute the sql insert or update sql statement
		st=con.prepareStatement(q);
		int i = 1;
		for(String name : getSQLColumns().getOrderedKeys()) {
			putField(st, i, getSQLColumns().get(name), getModelAccessor().get(model, name));
			i++;
		}
//		if(locale!=null) 
//			putField(st, i, I18N_COLUMN_NAME, locale);
		
		boolean ret = st.execute();
		sql.sqlClose(st, con);
		if(ret) {
			return getModelId(model);
		}
		else {
			return -1;
		}	
		
	} catch (Exception e) {
		throw new DaoException(e);
	}		
}
//protected long save(T model, boolean insert) throws DaoException {
//	return save(model, insert, null);
//}
public List<T> listAll() throws DaoException {
	try {
//		if(locale!=null)
//			checkI18n();
		List<T> l = new LinkedList<T>();
		Connection con = getConnection();
		Statement st = con.createStatement();
		String q = "select * FROM "+getTableName();
		
//		if(locale!=null)
//			q+=" WHERE "+I18N_COLUMN_NAME+"='"+locale+"'";
		
		ResultSet rs = st.executeQuery(q);
		T e = buildFrom(rs);
		while(e!=null) {
			l.add(e);
			e = buildFrom(rs);
		}		
		sql.sqlClose(rs, st, con);
		return l;
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}
//public List<T> listAll() throws DaoException {
//	return listAll(null);
//}
public T remove(long id) throws DaoException {
	try {
//		if(locale!=null)
//			checkI18n();
		T t = get(id);
		Connection con = getConnection();
		Statement st = con.createStatement();
		String q = "DELETE FROM "+getTableName()+" WHERE "+getColumnIdName()+"="+id;
//		if(locale!=null)
//			q+=" AND "+I18N_COLUMN_NAME+"='"+locale+"'";
		st.execute(q);
		sql.sqlClose(st, con);
		return t;
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}
//public T remove(long id) throws DaoException {
//	return remove(id, null);
//}

//public T remove(T t, String locale) throws DaoException {
//	return remove(getModelId(t), locale);//t.getModeld());
//}
public T remove(T t) throws DaoException {
	return remove(getModelId(t));//t.getModeld());
}

public void update(T t) throws DaoException {
	save(t, false);
}
//public void update(T t, String locale) throws DaoException {
//	save(t, false, locale);
//}
public void dropTable() throws DaoException {
	sql.tableDrop(getTableName());
}
/**
 * utility for extracting a result set 's field value
 * @param rs
 * @param colName
 * @param type
 * @return
 * @throws DaoException
 */
protected static Object getField(ResultSet rs, String colName, String type) throws DaoException  {
	try {
		return rs.getObject(colName);
//		if(type.equals(SQLUtil.TYPE_LONG))
//			return rs.getLong(colName);
//		else if(type.equals(SQLUtil.TYPE_DATETIME))
//			return rs.getDate(colName);
//		else if(type.equals(SQLUtil.TYPE_BOOLEAN))
//			return rs.getBoolean(colName);
//		else if(type.equals(SQLUtil.TYPE_STRING)||type.equals(SQLUtil.TYPE_STRING_LONG))
//			return rs.getString(colName);		
//		else
//			throw new DaoException("sql type not supported : "+type);
	} catch (Exception e) {
		throw new DaoException(e);
	}
}
/**
 * 

 * @param st
 * @param colName
 * @param type
 * @param value
 * @throws DaoException
 */
protected static void putField(PreparedStatement st, int paramIndex, String type, Object value) throws DaoException {
	try {
		st.setObject(paramIndex, value);
//		if(type.equals(SQLUtil.TYPE_LONG))
//			st.setLong(paramIndex, (Long)value);
//		else if(type.equals(SQLUtil.TYPE_DATETIME))
//			st.setTimestamp(paramIndex, new java.sql.Timestamp(((Date)value).getTime()));
//		else if(type.equals(SQLUtil.TYPE_BOOLEAN))
//			st.setBoolean(paramIndex, (Boolean)value);
//		else if(type.equals(SQLUtil.TYPE_STRING)||type.equals(SQLUtil.TYPE_STRING_LONG))
//			st.setString(paramIndex, (String)value);		
//		
//		//TODO: all other types
//		else
//			throw new DaoException("sql type not supported : "+type);
	} catch (Exception e) {
		throw new DaoException(e);
	}
}

public T buildFrom(ResultSet rs) throws DaoException {
	
	try {
//		System.out.print("buildFrom - "+rs.getMetaData().getColumnCount()+" - "+getSQLColumns().getOrderedKeys().size());
		if(rs.next()) {			
//			T instance = null;
			T instance  = instantiateModel();
			for(String name : getSQLColumns().getOrderedKeys()) {
				Object value = getField(rs, name, getSQLColumns().get(name));
//				System.out.println("name: "+name+", value: "+value+"\n");
//				instance  = instantiateModel();
				getModelAccessor().set(instance, name, value);
			}
			return instance;
		}
		else {
			return null;
		}
	} catch (Exception e) {
		throw new DaoException(e);
	}	
}






//public T get(long id, String locale) throws DaoException {
//	if(!sql.tableHasColumn(getTableName(), I18N_COLUMN_NAME)) {
//		return get(id);
//	}
//	else {
//		sql.query(getTableName(), Util.<Object>toOrderedMap(
//			getColumnIdName(), id,
//			I18N_COLUMN_NAME, locale
//		));
//		return 
//	}
//}





/* *** search utilities *** */

/**
 * String tableName, String idColName, OrderedMap<String, String> keywords, StringMatcher matcher, 
			String operator) throws DaoException {
 * @return
 */
public List<T> search(OrderedMap<String, String> keywords, 
	SQLUtil.StringMatcher matcher, String operator) throws DaoException {
//	return get(sql.search(getTableName(), getColumnIdName(), keywords, matcher, operator));
	return searchLocale(keywords, matcher, operator, null);
}
public List<T> search(OrderedMap<String, String> keywords, String operator) throws DaoException {
	return search(keywords, SQLUtil.defaultStringMatcher, operator);
}
public List<T> search(OrderedMap<String, String> keywords) throws DaoException {
	return search(keywords, SQLUtil.defaultStringMatcher, "and");
}
public List<T> searchLocale(OrderedMap<String, String> keywords, SQLUtil.StringMatcher matcher, String operator, String locale) throws DaoException {
//	if(locale==null)
//		locale=SQLUtil.I18N_DEFAULT_LOCALE;
	OrderedMap<Object, Object> match = locale==null ? null : Util.<Object>toOrderedMap(I18N_COLUMN_NAME, locale);
	return getLocales(sql.search(getTableName(), getColumnIdName(), match, keywords, matcher, operator), locale);
}
public List<T> searchLocale(OrderedMap<String, String> keywords, String locale) throws DaoException {
	return searchLocale(keywords, SQLUtil.defaultStringMatcher, null, locale);
}
}